package org.ccccye.multilevel.service;

import com.google.common.collect.Lists;
import org.ccccye.multilevel.pojo.*;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.util.Assert;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;

/**
 * 无限级分类
 * 方案二： Closure table法
 */
@Service
public class MultiLevelService2 implements MultiLevelService{
    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public List selectList(Integer id) {
        String sql = "select p.prod_id as prodId, p.prod_name as prodName from prod_category_tree t join prod_category_2 p on t.descendant = p.prod_id where t.ancestor = ?";
        List<Category2> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Category2.class), id);
        return list;
    }

    @Override
    public List selectListForTree(Integer id) {
        Assert.notNull(id, "id不能为null");

        String sql = "select p.prod_id as prodId, p.prod_name as prodName, t2.ancestor as pid from prod_category_tree t1 join prod_category_tree t2 on t2.descendant = t1.descendant join prod_category_2 p on t1.descendant = p.prod_id where t1.ancestor = ? and t2.descendant = t1.descendant and t2.distance = 1";
        List<Category2> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Category2.class), id);
        List<CategoryTreeNode2> treeNodeList = list.stream().map(category2 -> {
            try {
                CategoryTreeNode2 tmp = CategoryTreeNode2.class.newInstance();
                BeanUtils.copyProperties(category2, tmp);
                return tmp;
            } catch (InstantiationException e) {
                e.printStackTrace();
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            }
            return null;
        }).collect(Collectors.toList());

        CategoryTreeNode2 treeNode = treeNodeList.stream().filter(c -> id.equals(c.getProdId())).collect(Collectors.toList()).get(0);

        List<CategoryTreeNode2> treeNode2List = buildTree(treeNodeList, id);
        if (treeNode != null){
            treeNode.setChildList(treeNode2List);
            return Lists.newArrayList(treeNode);
        }

        return treeNode2List;
    }

    @Override
    public List selectPeerById(Integer id) {
        String sql = "select p.prod_id as prodId, p.prod_name as prodName from prod_category_tree t1 join prod_category_tree t2 on t1.ancestor = t2.ancestor and t2.distance = 1 join prod_category_2 p on p.prod_id = t2.descendant where t1.descendant = ? and t1.distance = 1";
        List<Category2> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Category2.class), id);
        return list;
    }

    @Override
    public List selectChildById(Integer id) {
        String sql = "select p.prod_id as prodId, p.prod_name as prodName from prod_category_tree t join prod_category_2 p on t.descendant = p.prod_id where ancestor = ? and distance = 1";
        List<Category2> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Category2.class), id);
        return list;
    }

    @Override
    public Integer deepById(Integer id) {
        String sql = "select distance from prod_category_tree where ancestor = 0 and descendant = ?";
        Integer ret = jdbcTemplate.queryForObject(sql, Integer.class, id);
        return ret;
    }

    @Override
    public Integer insertNode(Category category) {
        if (!(category instanceof Category2)){
            return 0;
        }
        Category2 cg = (Category2) category;
        int rowCount = 0;
        String sql = "INSERT INTO prod_category_tree(ancestor,descendant,distance) (SELECT ancestor,?,distance+1 FROM prod_category_tree WHERE descendant=?)";
        rowCount += jdbcTemplate.update(sql, cg.getProdId(), cg.getPid());

        // 然后就是加入自身连接的记录。
        sql = "INSERT INTO prod_category_tree(ancestor,descendant,distance) VALUES(?,?,0)";
        rowCount += jdbcTemplate.update(sql, cg.getProdId(), cg.getProdId());

        sql = "INSERT INTO prod_category_2(prod_id, prod_name) values(?,?)";
        rowCount += jdbcTemplate.update(sql, cg.getProdId(), cg.getProdName());

        return rowCount;
    }

    @Override
    public Integer updateNode(Category category) {
        // 规定只能更改prod_name，如果需要更改结构，那需要先删除再增加
        if (!(category instanceof Category2)){
            return 0;
        }
        Category2 cg = (Category2) category;

        String sql ="UPDATE prod_category_2 SET prod_name = ? where prod_id = ?";
        int rowCount = jdbcTemplate.update(sql, cg.getProdName(), cg.getProdId());
        return rowCount;
    }

    @Override
    public Integer deleteNode(Category category) {
        if (!(category instanceof Category2)){
            return 0;
        }
        Category2 cg = (Category2) category;

        // 事务
        int rowCount = 0;

        // 删除prod_category_2 表
        String sql ="delete from prod_category_2 where prod_id = ?";
        rowCount += jdbcTemplate.update(sql, cg.getProdId());

        // 删除prod_category_tree表
        sql ="select t2.* from prod_category_tree t1 join prod_category_tree t2 on t1.descendant = t2.descendant where t1.ancestor = ?";
        List<CategoryTree> treeList = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(CategoryTree.class), cg.getProdId());

        // 批量删除
        sql = "delete from prod_category_tree where ancestor = ? and descendant = ?";
        int[] retList = jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement preparedStatement, int i) throws SQLException {
                CategoryTree tree = treeList.get(i);
                preparedStatement.setInt(1, tree.getAncestor());
                preparedStatement.setInt(2, tree.getDescendant());
            }

            @Override
            public int getBatchSize() {
                return treeList.size();
            }
        });

        for (int ret : retList){
            rowCount += ret;
        }

        return rowCount;
    }

    /**
     * 构建树结构
     * @param list
     * @param parentId
     * @return
     */
    private List<CategoryTreeNode2> buildTree(List<CategoryTreeNode2> list, Integer parentId){
        List<CategoryTreeNode2> result = new ArrayList<>();
        for (CategoryTreeNode2 treeNode : list){
            Integer prodId = treeNode.getProdId();
            Integer pid = treeNode.getPid();
            if (parentId.intValue() == pid.intValue()){
                List<CategoryTreeNode2> childList = buildTree(list, prodId);

                treeNode.setChildList(childList);
                result.add(treeNode);
            }
        }
        return result;
    }
}
